/*
MariaDB Backup
Database: dscape
Backup Time: 2022-07-10 22:20:06
*/

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `dscape`.`black_list`;
DROP TABLE IF EXISTS `dscape`.`compute_history`;
DROP TABLE IF EXISTS `dscape`.`patient_data`;
DROP TABLE IF EXISTS `dscape`.`role_permission`;
DROP TABLE IF EXISTS `dscape`.`user_basic`;
CREATE TABLE `black_list` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `type` tinyint(1) NOT NULL COMMENT '黑名单数据类型；\r\n0：ip；\r\n1：uid',
  `name` varchar(255) NOT NULL COMMENT '黑名单条目名称，可空',
  `data` varchar(255) NOT NULL COMMENT '黑名单数据',
  `create_time` datetime NOT NULL DEFAULT current_timestamp(),
  `edit_time` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `del` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`id`,`data`) USING BTREE,
  UNIQUE KEY `index_id` (`id`) USING BTREE,
  KEY `index_data` (`data`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `compute_history` (
  `id` int(11) NOT NULL,
  `uid` char(36) NOT NULL COMMENT '计算数据记录对应的用户uid',
  `rid` int(11) NOT NULL COMMENT '计算数据记录对应的患者数据id',
  PRIMARY KEY (`id`,`uid`,`rid`) USING BTREE,
  UNIQUE KEY `index_id` (`id`) USING BTREE,
  KEY `index_uid` (`uid`) USING BTREE,
  KEY `index_pid` (`rid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `patient_data` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '数据id',
  `pid` int(15) NOT NULL COMMENT '病人id',
  `ctDNA_length` int(4) NOT NULL COMMENT 'ctDNA长度',
  `CpG` int(5) NOT NULL COMMENT '甲基化位点数',
  `hcc_status` tinyint(1) NOT NULL COMMENT '是否为HCC，0: 否, 1: 是',
  `hcc_infer_status` tinyint(1) NOT NULL COMMENT '通过推断得出的HCC状态',
  `create_time` datetime NOT NULL DEFAULT current_timestamp(),
  `edit_time` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `delete` tinyint(1) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`,`pid`) USING BTREE,
  UNIQUE KEY `id` (`id`) USING BTREE,
  KEY `pid` (`pid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `role_permission` (
  `id` int(4) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL COMMENT '权限名称',
  `permit` varchar(255) NOT NULL COMMENT '许可的用户角色，角色之间用";"（分号）隔开',
  `create_time` datetime NOT NULL DEFAULT current_timestamp(),
  `edit_time` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `del` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`,`name`),
  UNIQUE KEY `index_id` (`id`) USING BTREE,
  UNIQUE KEY `index_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `user_basic` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` char(36) NOT NULL COMMENT '用户唯一uuid',
  `user_name` varchar(32) NOT NULL COMMENT '用户用户名',
  `password` varchar(60) NOT NULL COMMENT '用户密码',
  `role` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '用户身份，默认为0；\r\n0：普通用户，\r\n1：管理员\r\n',
  `status` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '用户状态；0：正常；1：封禁；2：注销（销号）',
  `create_time` datetime NOT NULL DEFAULT current_timestamp(),
  `edit_time` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`,`uid`,`user_name`),
  UNIQUE KEY `id` (`id`) USING BTREE,
  UNIQUE KEY `uid` (`uid`) USING BTREE,
  UNIQUE KEY `user_name` (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
BEGIN;
LOCK TABLES `dscape`.`black_list` WRITE;
DELETE FROM `dscape`.`black_list`;
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `dscape`.`compute_history` WRITE;
DELETE FROM `dscape`.`compute_history`;
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `dscape`.`patient_data` WRITE;
DELETE FROM `dscape`.`patient_data`;
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `dscape`.`role_permission` WRITE;
DELETE FROM `dscape`.`role_permission`;
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `dscape`.`user_basic` WRITE;
DELETE FROM `dscape`.`user_basic`;
UNLOCK TABLES;
COMMIT;
